package course.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import course.db.DbConnection;
<<<<<<< HEAD
import course.controller.CourseControllerListener;
import course.dao.IStudentDao;
import course.po.Course;
import course.po.Student;
import course.po.Teacher;
import course.view.StudentFrame;

public class StudentDaoImpl implements IStudentDao {

	/**
	 *
	 * 新增学生信息
	 *
	 */
	@Override
	public void save(Student student) {
		Connection conn = DbConnection.getConnection();
		Course course = student.getCourse();
		int stuId = student.getStuId();
		String stuNo = student.getStuNo();
		String stuName = student.getStuName();
		String stuSex = student.getStuSex();
		Date stuAge = student.getStuAge();
		String stuMajor = student.getStuMajor();
		int stuClassNo = student.getStuClassNo();
		int stuGrade = student.getStuGrade();
		int couId = course.getCouId();
		String sql = "INSERT INTO student VALUES(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, stuId);
			ps.setString(2, stuNo);
			ps.setString(3, stuName);
			ps.setString(4, stuSex);
			ps.setDate(5, (java.sql.Date) stuAge);
			ps.setInt(6, stuClassNo);
			ps.setString(7, stuMajor);
			ps.setInt(8, couId);
			ps.setInt(9, stuGrade);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
	}

	/**
	 *
	 * 更新学生信息
	 *
	 */
	@Override
	public void update(Student student) {
		Connection conn = DbConnection.getConnection();
		Course course = student.getCourse();
		int stuId = student.getStuId();
		String stuNo = student.getStuNo();
		String stuName = student.getStuName();
		String stuSex = student.getStuSex();
		Date stuAge = student.getStuAge();
		String stuMajor = student.getStuMajor();
		int stuClassNo = student.getStuClassNo();
		int couId = course.getCouId();
		String couName = course.getCouName();
		int stuGrade = student.getStuGrade();
		String sql = "UPDATE student,course SET student_no=?,student_name=?,student_sex=?,student_age=?"
				+ ",student_class_no=?,student_major=?,student_course_id=?,course_name= ?,student_grade=? WHERE student.student_course_id=course.course_id AND student_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stuNo);
			ps.setString(2, stuName);
			ps.setString(3, stuSex);
			ps.setDate(4, (java.sql.Date) stuAge);
			ps.setInt(5, stuClassNo);
			ps.setString(6, stuMajor);
			ps.setInt(7, couId);
			ps.setString(8, couName);
			ps.setInt(9, stuGrade);
			ps.setInt(10, stuId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 *
	 * 删除学生信息
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM student WHERE student_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 * 根据编号查询学生的信息 student_id为主键
	 *
	 */
	@Override
	public Student getOne(String name) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course  WHERE student.student_course_id = course.course_id AND student_name=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				String stuSex = rs.getString(4);
				Date stuAge = rs.getDate(5);
				int stuClassNo = rs.getInt(6);
				String stuMajor = rs.getString(7);
				int couId = rs.getInt(8);
				String couName = rs.getString(9);
				int stuGrade = rs.getInt(10);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,
						course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList.get(0);
	}

	/**
	 * 根据编号查询学生的信息 student_id为主键
	 *
	 */
	@Override
	public Student get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course  WHERE student.student_course_id = course.course_id AND student_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				String stuSex = rs.getString(4);
				Date stuAge = rs.getDate(5);
				int stuClassNo = rs.getInt(6);
				String stuMajor = rs.getString(7);
				int couId = rs.getInt(8);
				String couName = rs.getString(9);
				int stuGrade = rs.getInt(10);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,
						course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList.get(0);
	}

	// 根据学生编号查询学生个人信息
	public Student getStudent(String stuNum) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major FROM student WHERE student_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stuNum);
			rs = ps.executeQuery();
			while (rs.next()) {
				String stuNo = rs.getString(1);
				String stuName = rs.getString(2);
				String stuSex = rs.getString(3);
				Date stuAge = rs.getDate(4);
				int stuClassNo = rs.getInt(5);
				String stuMajor = rs.getString(6);
				Student student = new Student(stuNo, stuName, stuSex, stuAge, stuClassNo, stuMajor);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList.get(0);
	}

	/**
	 *
	 * 查询所有学生的信息
	 */
	@Override
	public List<Student> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
				+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course  WHERE student.student_course_id=course.course_id AND student_course_id='0' ORDER BY student_class_no,student_grade";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int stuId = rs.getInt(1);
				String stuNo = rs.getString(2);
				String stuName = rs.getString(3);
				String stuSex = rs.getString(4);
				Date stuAge = rs.getDate(5);
				int stuClassNo = rs.getInt(6);
				String stuMajor = rs.getString(7);
				int couId = rs.getInt(8);
				String couName = rs.getString(9);
				int stuGrade = rs.getInt(10);
				Course course = new Course(couId, couName);
				Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,
						course);
				studentList.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return studentList;
	}

		//学生选课界面显示所有已选的课程
		public List<Course> getSelectedCourse(String stuNo) {
				Connection conn = DbConnection.getConnection();
				List<Course> courseList = new ArrayList<Course>();
//				String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit,course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,teacher where teacher_course_id=course_id AND course_no NOT IN("
//					     + "SELECT course_no FROM course,student WHERE student.student_course_id=course.course_id AND student_no = ?)";
				String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit,course_teacher_id,teacher_name,course_max_number,course_select_number FROM course,student,teacher where student.student_course_id=course.course_id AND teacher_id=course_teacher_id AND student_no=?";
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					ps.setString(1,stuNo);
					rs = ps.executeQuery();
					while (rs.next()) {
						int couId = rs.getInt(1);
						String couType = rs.getString(2);
						String couNo = rs.getString(3);
						String couName = rs.getString(4);
						String couDetail = rs.getString(5);
						int couCredit = rs.getInt(6);
						int teaId = rs.getInt(7);
						String teaName = rs.getString(8);
						int maxNumber = rs.getInt(9);
						int selectNumber = rs.getInt(10);
						 Teacher teacher  = new Teacher(teaId,teaName);
						Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit, maxNumber, selectNumber,teacher);
						courseList.add(course);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnection.close(rs, ps);
				}
		    return courseList;
			}
		
	// 查看课程某一学期总学分
	public int getCredit(String term) {
		Connection conn = DbConnection.getConnection();
		String sql = "SELECT course_credit FROM course WHERE course_id IN (SELECT course_id FROM course,student WHERE  student.student_course_id=course.course_id AND student_no=?) AND course_Type= ?";
		int credit = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,CourseControllerListener.nameInput);
			ps.setString(2, term);
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					credit = credit + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return credit;
	}

	// 查看课程所有所有学期总学分
	public int getAllCredit() {
		Connection conn = DbConnection.getConnection();
		String sql ="SELECT course_credit FROM course WHERE course_id IN (SELECT course_id FROM course,student WHERE  student.student_course_id=course.course_id AND student_no=?) ";
		int credit = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,CourseControllerListener.nameInput);
			rs = ps.executeQuery();
			List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
			while (rs.next()) {// 如果有数据，取第一列添加如list
				list.add(rs.getInt(1));
			}
			if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
				Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
				for (int i = 0; i < list.size(); i++) {
					arr[i] = list.get(i);// 数组赋值了。
				}
				for (int i = 0; i < list.size(); i++) {
					credit = credit + arr[i];
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return credit;
	}
	// 查看课程所有所有学期平均分
	    @Override
	 	public int getAverage() {
			Connection conn = DbConnection.getConnection();
			String sql ="SELECT AVG(student_grade) FROM student WHERE student_no=?";
			int grade = 0;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,CourseControllerListener.nameInput);
				rs = ps.executeQuery();
				List<Integer> list = new ArrayList<Integer>();// 创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
				while (rs.next()) {// 如果有数据，取第一列添加如list
					list.add(rs.getInt(1));
				}
				if (list != null && list.size() > 0) {// 如果list中存入了数据，转化为数组
					Integer[] arr = new Integer[list.size()];// 创建一个和list长度一样的数组
					for (int i = 0; i < list.size(); i++) {
						arr[i] = list.get(i);// 数组赋值了。
					}
					for (int i = 0; i < list.size(); i++) {
						grade = grade + arr[i];
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(rs, ps);
			}
			return grade;
		}

	/**
	 * 根据学生课程名称查询学生成绩
	 *
	 */

	public int getMyCourseNoGrade(String stuCouName) {
		Connection conn = DbConnection.getConnection();
		String sql = "SELECT student_grade FROM course,student WHERE course_id = student.student_course_id AND course_name LIKE CONCAT('%',?,'%')";
		int stuGrade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, stuCouName);
			rs = ps.executeQuery();
			while (rs.next()) {
				stuGrade = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return stuGrade;
	}

	/**
	 *
	 * 新增学生选课记录信息
	 *
	 */
	@Override
	public void saveStuCou(Student student) {
		int stuId = getNum()+1;
		Connection conn = DbConnection.getConnection();
		Course course = student.getCourse();
	
		String stuNo = student.getStuNo();
		String stuName = student.getStuName();
		String stuSex = student.getStuSex();
		Date stuAge = student.getStuAge();
		String stuMajor = student.getStuMajor();
		int stuClassNo = student.getStuClassNo();
		int stuGrade = student.getStuGrade();
		int couId = Integer.valueOf(StudentFrame.t2.getText());
		String sql = "INSERT INTO student VALUES(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, stuId);
			ps.setString(2, stuNo);
			ps.setString(3, stuName);
			ps.setString(4, stuSex);
			ps.setDate(5, (java.sql.Date) stuAge);
			ps.setInt(6, stuClassNo);
			ps.setString(7, stuMajor);
			ps.setInt(8, couId);
			ps.setInt(9, stuGrade);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
	}

	/**
	 * 查看学生表有多少条记录
	 *
	 */

	public int getNum() {
		Connection conn = DbConnection.getConnection();
		String sql = "SELECT count(*) FROM student ";
		int stuGrade = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				stuGrade = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return stuGrade;
	}


	  /**
       *
       *删除学生选课记录信息——退课
       */
      @Override
      public void deleteNum(int cI,String stuNo) {
	      Connection conn = DbConnection.getConnection();
	      String sql = "DELETE FROM student WHERE student.student_course_id=? AND student_no=? ";
	      PreparedStatement ps = null;
	     try {
	    	ps = conn.prepareStatement(sql);
		    ps.setInt(1,cI);
			ps.setString(2,CourseControllerListener.nameInput);
		    ps.executeUpdate();
	       } catch (SQLException e) {
		       e.printStackTrace();
	       } finally {
		      DbConnection.close(null, ps);
	      }
      }

	    /**
	 	*
	 	*学生已选课状态
	 	*
	 	*/
	     @Override
	 	public void hadSelectNum() {
	 		Connection conn = DbConnection.getConnection();
	 		String sql = "update course set course_select_number= course_select_number +1  WHERE course_id=?";
	 		PreparedStatement ps = null;
	 		try {
	 			ps = conn.prepareStatement(sql);
	 			ps.setInt(1, Integer.valueOf(StudentFrame.t2.getText()));
	 			ps.executeUpdate();
	 		} catch (SQLException e) {
	 			e.printStackTrace();
	 		} finally {
	 			DbConnection.close(null, ps);
	 		}
	 	}
}
=======
import course.dao.IStudentDao;
import course.po.Course;
import course.po.Student;

public class StudentDaoImpl implements IStudentDao{
    
	public class StudentDaoImpl implements IStudentDao{
	    
		/**
		*
		*新增学生信息
		*
		*/
		@Override
		public void save(Student student) {
			Connection conn = DbConnection.getConnection();
			Course course = student.getCourse();
			int stuId = student.getStuId();
			String stuNo = student.getStuNo();
			String stuName = student.getStuName();
			String  stuSex = student.getStuSex();
			Date stuAge = student.getStuAge();
			String stuMajor = student.getStuMajor();
			int stuClassNo = student.getStuClassNo();
			int stuGrade = student.getStuGrade();
			int couId = course.getCouId();
			String sql = "INSERT INTO student VALUES(?,?,?,?,?,?,?,?,?)";
			PreparedStatement ps = null;
			try {
				ps  = conn.prepareStatement(sql);
				ps.setInt(1,stuId);
				ps.setString(2,stuNo);
				ps.setString(3,stuName);
				ps.setString(4,stuSex);
				ps.setDate(5,(java.sql.Date)stuAge);
				ps.setInt(6,stuClassNo);
				ps.setString(7,stuMajor);
				ps.setInt(8,couId);
				ps.setInt(9,stuGrade);
				ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				DbConnection.close(null, ps);
			}	
		}

		/**
		*
		*更新学生信息
		*
		*/
		@Override
		public void update(Student student) {
			Connection conn = DbConnection.getConnection();
			Course course =  student.getCourse();
			int stuId = student.getStuId();
			String stuNo = student.getStuNo();
			String stuName = student.getStuName();
			String  stuSex = student.getStuSex();
			Date stuAge = student.getStuAge();
			String stuMajor = student.getStuMajor();
			int stuClassNo = student.getStuClassNo();
			int couId = course.getCouId();
			String couName = course.getCouName();
			int stuGrade = student.getStuGrade();
			String sql = "UPDATE student,course SET student_no=?,student_name=?,student_sex=?,student_age=?" + 
				          ",student_class_no=?,student_major=?,student_course_id=?,course_name= ?,student_grade=? WHERE student.student_course_id=course.course_id AND student_id=?";
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,stuNo);
				ps.setString(2,stuName);
				ps.setString(3,stuSex);
				ps.setDate(4,(java.sql.Date)stuAge);
				ps.setInt(5,stuClassNo);
				ps.setString(6,stuMajor);
				ps.setInt(7,couId);
				ps.setString(8,couName);
				ps.setInt(9,stuGrade);
				ps.setInt(10,stuId);
				ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(null, ps);
			}
			
		}

		/**
		*
		*删除学生信息
		*/
		@Override
		public void delete(int id) {
			Connection conn = DbConnection.getConnection();
			String sql = "DELETE FROM student WHERE student_id=?";
			PreparedStatement ps = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(null, ps);
			}
			
		}
		/**
		 * 根据编号查询学生的信息 student_id为主键
		 *
		 */
		@Override
		public Student getOne(String name) {
			Connection conn = DbConnection.getConnection();
			List<Student> studentList = new ArrayList<Student>();
			String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
					+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course  WHERE student.student_course_id = course.course_id AND student_name=?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, name);
				rs = ps.executeQuery();
				while (rs.next()) {
					int stuId = rs.getInt(1);
					String stuNo = rs.getString(2);
					String stuName = rs.getString(3);
					String stuSex = rs.getString(4);
					Date stuAge = rs.getDate(5);
					int stuClassNo = rs.getInt(6);
					String stuMajor = rs.getString(7);
					int couId = rs.getInt(8);
					String couName = rs.getString(9);
					int stuGrade = rs.getInt(10);
					Course course = new Course(couId,couName);
					Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,course);
					studentList.add(student);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(rs, ps);
			}
			return studentList.get(0);
		}
		/**
		 * 根据编号查询学生的信息 student_id为主键
		 *
		 */
		@Override
		public Student get(int id) {
			Connection conn = DbConnection.getConnection();
			List<Student> studentList = new ArrayList<Student>();
			String sql = "SELECT student_id,student_no,student_name,student_sex,student_age"
					+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course  WHERE student.student_course_id = course.course_id AND student_id=?";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs = ps.executeQuery();
				while (rs.next()) {
					int stuId = rs.getInt(1);
					String stuNo = rs.getString(2);
					String stuName = rs.getString(3);
					String stuSex = rs.getString(4);
					Date stuAge = rs.getDate(5);
					int stuClassNo = rs.getInt(6);
					String stuMajor = rs.getString(7);
					int couId = rs.getInt(8);
					String couName = rs.getString(9);
					int stuGrade = rs.getInt(10);
					Course course = new Course(couId,couName);
					Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,course);
					studentList.add(student);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(rs, ps);
			}
			return studentList.get(0);
		}
		
		//根据学生编号查询学生个人信息
			public  Student getStudent(String stuNum) {
				Connection conn = DbConnection.getConnection();
				List<Student> studentList = new ArrayList<Student>();
				String sql = "SELECT student_no,student_name,student_sex,student_age"
						+ ",student_class_no,student_major FROM student WHERE student_no=?";
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					ps.setString(1, stuNum);
					rs = ps.executeQuery();
					while (rs.next()) {
						String stuNo = rs.getString(1);
						String stuName = rs.getString(2);
						String stuSex = rs.getString(3);
						Date stuAge = rs.getDate(4);
						int stuClassNo = rs.getInt(5);
						String stuMajor = rs.getString(6);
						Student student = new Student(stuNo,stuName, stuSex,stuAge,stuClassNo,stuMajor);
						studentList.add(student);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnection.close(rs, ps);
				}
				return studentList.get(0);
			}
			/**
			 *
			 * 查询所有学生的信息
			 */
			@Override
			public List<Student> findAll() {
				Connection conn = DbConnection.getConnection();
				List<Student> studentList = new ArrayList<Student>();
				String sql ="SELECT student_id,student_no,student_name,student_sex,student_age"
						+ ",student_class_no,student_major,student_course_id,course_name,student_grade FROM student,course  WHERE student.student_course_id=course.course_id GROUP BY student_class_no ASC,student_grade DESC";
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					rs = ps.executeQuery();
					while (rs.next()) {
						int stuId = rs.getInt(1);
						String stuNo = rs.getString(2);
						String stuName = rs.getString(3);
						String stuSex = rs.getString(4);
						Date stuAge = rs.getDate(5);
						int stuClassNo = rs.getInt(6);
						String stuMajor = rs.getString(7);
						int couId = rs.getInt(8);
						String couName = rs.getString(9);
						int stuGrade = rs.getInt(10);
						Course course = new Course(couId,couName);
						Student student = new Student(stuId, stuNo, stuName, stuSex, stuAge, stuMajor, stuClassNo, stuGrade,course);
						studentList.add(student);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnection.close(rs, ps);
				}
				return studentList;
			}
			//学生选课界面显示所有未选的课程
			public List<Course> getNotSelectedCourse(String stuNo) {
					Connection conn = DbConnection.getConnection();
					List<Course> courseList = new ArrayList<Course>();
					String sql = "SELECT course_id,course_type,course_no,course_name,course_time_place,course_credit"
							+ ",course_teacher_id,teacher_name,course_max_number,course_select_number FROM student,course,teacher WHERE "
							+ "student.student_course_id=course.course_id=teacher_course_id AND teacher_id = course_teacher_id AND course_no NOT IN(SELECT  course_no FROM student,course  WHERE student.student_course_id=course.course_id AND student_no=?)";
					PreparedStatement ps = null;
					ResultSet rs = null;
					try {
						ps = conn.prepareStatement(sql);
						ps.setString(1,stuNo);
						rs = ps.executeQuery();
						while (rs.next()) {
							int couId = rs.getInt(1);
							String couType = rs.getString(2);
							String couNo = rs.getString(3);
							String couName = rs.getString(4);
							String couDetail = rs.getString(5);
							int couCredit = rs.getInt(6);
							int teaId = rs.getInt(7);
							String teaName = rs.getString(8);
							int maxNumber = rs.getInt(9);
							int selectNumber = rs.getInt(10);
							 Teacher teacher  = new Teacher(teaId,teaName);
							Course course = new Course(couId, couType, couNo, couName, couDetail, couCredit, maxNumber, selectNumber,teacher);
							courseList.add(course);
						}
					} catch (SQLException e) {
						e.printStackTrace();
					} finally {
						DbConnection.close(rs, ps);
					}
			    return courseList;
				}
			//查看课程某一学期总学分
			public int getCredit(String term) {
				Connection conn = DbConnection.getConnection();
				String sql = "SELECT course_credit FROM course WHERE course_Type= ?";
				int credit =0;
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					ps.setString(1,term);
					rs = ps.executeQuery();
						List<Integer> list=new ArrayList<Integer>();//创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
						   while (rs.next()) {//如果有数据，取第一列添加如list
						    list.add(rs.getInt(1));
						   }
						   if(list != null && list.size()>0){//如果list中存入了数据，转化为数组
							   Integer[] arr=new Integer[list.size()];//创建一个和list长度一样的数组
						    for(int i=0;i<list.size();i++){
						     arr[i]=list.get(i);//数组赋值了。
						    }
						    for(int i=0;i<list.size();i++) {
						    	credit = credit+arr[i];
						    }
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnection.close(rs, ps);
				}
		    return credit;
			}
			
			//查看课程所有所有学期总学分
			public int getAllCredit() {
				Connection conn = DbConnection.getConnection();
				String sql = "SELECT course_credit FROM course ";
				int credit =0;
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					rs = ps.executeQuery();
						List<Integer> list=new ArrayList<Integer>();//创建取结果的列表，之所以使用列表，不用数组，因为现在还不知道结果有多少，不能确定数组长度，所有先用list接收，然后转为数组
						   while (rs.next()) {//如果有数据，取第一列添加如list
						    list.add(rs.getInt(1));
						   }
						   if(list != null && list.size()>0){//如果list中存入了数据，转化为数组
							   Integer[] arr=new Integer[list.size()];//创建一个和list长度一样的数组
						    for(int i=0;i<list.size();i++){
						     arr[i]=list.get(i);//数组赋值了。
						    }
						    for(int i=0;i<list.size();i++) {
						    	credit = credit+arr[i];
						    }
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnection.close(rs, ps);
				}
		    return credit;
			}
			/**
			 * 根据学生课程名称查询学生成绩
			 *
			 */

			public int getMyCourseNoGrade(String stuCouName) {
				Connection conn = DbConnection.getConnection();
				String sql = "SELECT student_grade FROM course WHERE course_name=?";
				int stuGrade = 0;
				PreparedStatement ps = null;
				ResultSet rs = null;
				try {
					ps = conn.prepareStatement(sql);
					ps.setString(1, stuCouName);
					rs = ps.executeQuery();
					while (rs.next()) {
						stuGrade = rs.getInt(1);
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					DbConnection.close(rs, ps);
				}
				return stuGrade;
			}
	}
}
>>>>>>> refs/remotes/origin/master
